wikibot
| Table | Production.WorkOrder |
| Description | Manufacturing work orders. |
Columns ¶
| Column | Data Type | Nullable | Default | Description |
| WorkOrderID | int | not null | | Primary key for WorkOrder records. |
| ProductID | int | not null | | Product identification number. Foreign key to Product.ProductID. |
| OrderQty | int | not null | | Product quantity to build. |
| StockedQty | | | | Quantity built and put in inventory. |
| ScrappedQty | smallint | not null | | Quantity that failed inspection. |
| StartDate | datetime | not null | | Work order start date. |
| EndDate | datetime | null | | Work order end date. |
| DueDate | datetime | not null | | Work order due date. |
| ScrapReasonID | smallint | null | | Reason for inspection failure. |
| ModifiedDate | datetime | not null | (getdate()) | Date and time the record was last updated. |
Primary Key
| Primary Key | Columns |
| PK_WorkOrder_WorkOrderID | WorkOrderID |
Indexes
| Index | Type | Columns |
| IX_WorkOrder_ProductID | | ProductID |
| IX_WorkOrder_ScrapReasonID | | ScrapReasonID |
Check Constraints
Foreign Keys
Detail Tables
Triggers
| Trigger | Type |
| iWorkOrder | ON INSERT |
| uWorkOrder | ON UPDATE |
Trigger iWorkOrder
CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]
AFTER INSERT AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
INSERT INTO [Production].[TransactionHistory](
[ProductID]
,[ReferenceOrderID]
,[TransactionType]
,[TransactionDate]
,[Quantity]
,[ActualCost])
SELECT
inserted.[ProductID]
,inserted.[WorkOrderID]
,'W'
,GETDATE()
,inserted.[OrderQty]
,0
FROM inserted;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
Trigger uWorkOrder
CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder]
AFTER UPDATE AS
BEGIN
DECLARE @Count int;
SET @Count = @@ROWCOUNT;
IF @Count = 0
RETURN;
SET NOCOUNT ON;
BEGIN TRY
IF UPDATE([ProductID]) OR UPDATE([OrderQty])
BEGIN
INSERT INTO [Production].[TransactionHistory](
[ProductID]
,[ReferenceOrderID]
,[TransactionType]
,[TransactionDate]
,[Quantity])
SELECT
inserted.[ProductID]
,inserted.[WorkOrderID]
,'W'
,GETDATE()
,inserted.[OrderQty]
FROM inserted;
END;
END TRY
BEGIN CATCH
EXECUTE [dbo].[uspPrintError];
-- Rollback any active or uncommittable transactions before
-- inserting information in the ErrorLog
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
EXECUTE [dbo].[uspLogError];
END CATCH;
END;
References